from django.db.models import Count, CharField, Value
from django.db import connection

from blog import models

def getRightInfo(request):
    # 浏览器右侧中的分类（其中values中的category是为了链接中获取category的id）
    r_category = models.Post.objects.values('category__category').annotate(count=Count('tag__tag',distinct=True)).order_by('category')
    r_tag = models.Post.objects.values('category__category','category','tag__tag').annotate(count=Count('*')).order_by('category','-count')
    # r_tag = enumerate(r_tag)
    # 浏览器右侧中的近期文章
    r_recent_post = models.Post.objects.all().order_by('-created')[:3]
    # 浏览器右侧中的归档
    cursor = connection.cursor()
    getpost = cursor.execute("select created,count(*) count from t_post group by strftime('%Y-%m',created) order by count desc")
    r_file_post = getpost.fetchall()
    return {'r_category':r_category,'r_tag':r_tag,'r_recent_post':r_recent_post,"r_file_post":r_file_post}